import sqlite3
from datetime import date
import pandas as pd
import plotly.graph_objects as go
from streamlit import session_state
import streamlit as st

#用于登录
@st.cache_resource
def get_conn(path:str):
    conn = sqlite3.connect(path,check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn
def login_page():
    st.title("个人财务管理系统")
    st.header("😄请登录您的账号")
    name = st.text_input("姓名",placeholder="姓名")
    passwd = st.text_input("密码",placeholder="密码",type="password")
    col1,col2 =st.columns(2)
    with col1:
        login_btn = st.button("登录",use_container_width=True)
    with col2:
        ret_btn = st.button("注册",use_container_width=True)
    #登录按钮
    if login_btn:
        conn = get_conn(r"student2.db")
        cur = conn.cursor()
        cur.execute("select * from users WHERE name= ? and password=?;", (name, passwd))
        ret = cur.fetchone()
        cur.close()
        if ret:
            st.session_state.page = "home"
            st.session_state.username = name
            st.rerun()
        else:
            st.error("登录失败，用户名或密码错误")
    #注册按钮
    if ret_btn:
        conn = get_conn(r"student2.db")
        cur = conn.cursor()
        cur.execute("select * from users WHERE name=? ;",(name,))
        if cur.fetchone():
            st.error("用户名已存在请重新输入")
        else:
            cur.execute("INSERT INTO users(name, password) VALUES (?, ?);", (name, passwd))
            conn.commit()
            st.success("注册成功请登录")
        cur.close()


def home_page():
    st.title(f"欢迎回来,{st.session_state.username}")
    conn_exp =get_conn("data.db")#连接数据库
    conn_exp.execute("""     
    CREATE TABLE IF NOT EXISTS expense(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      edate DATE NOT NULL,
      item TEXT NOT NULL,
      amount REAL NOT NULL,
      category TEXT NOT NULL
    );
    """)
    conn_exp.commit()
    #设置预算侧边栏
    if "budget" not in st.session_state:
        st.session_state.budget = 2000.0
        st.sidebar.subheader("✏每月预算")
        st.session_state.budget = st.sidebar.number_input(
            "预算(元）",min_value= 0.0 , value=session_state.budget,step=100.0
        )
    #记一笔
    st.sidebar.subheader("记一笔")
    with st.sidebar.form("add_expense"):
        edate = st.date_input("日期",value= date.today())
        item = st.text_input("消费项目",placeholder="午餐/打车/电影....")
        amount_str = st.text_input("金额（元）",value =  "" ,placeholder="请输入金额")
        try:
             amount = float(amount_str)
        except ValueError:
              st.error("请输入合法的数字！")
              amount = None
        #下拉列表
        category = st.selectbox("分类",["餐饮","交通","购物",'娱乐',"其他"])
        if st.form_submit_button("保存"):
           conn_exp.execute(
             "INSERT INTO expense(edate ,item,amount,category) VALUES(?,?,?,?)",
             (edate,item,-amount,category)
           )
           conn_exp.commit()
           st.rerun()
    #获取当前日期
    current_month = date.today().strftime("%Y-%m")
    #当月的消费绝对值加一起无记录就返回0存入spent

    spent = conn_exp.execute(
        "SELECT COALESCE(SUM(ABS(amount)), 0) FROM expense "
        "WHERE strftime('%Y-%m', edate) = ?", (current_month,)
    ).fetchone()[0]
    #预算
    budget = st.session_state.budget
    st.subheader(f"{current_month} 预算进度")  # 标题
    st.progress(min(spent / budget, 1.0))  # 进度
    if spent > budget:
        st.error(f"⚠️ 已超支：{spent:.2f} / {budget:.2f} 元")
    else:
        st.success(f"✅ 本月已花：{spent:.2f} / {budget:.2f} 元")


    tab1,tab2 = st.tabs(["支出表格","支出可视化"])
    df = pd.read_sql("SELECT * FROM expense ORDER BY id DESC", conn_exp)
    with tab1:
        if not df.empty:
            # 先把 id 列放出来，便于删除
            df_show = df.copy() #复制一份
            for _, row in df_show.iterrows(): #逐行遍历dataframe只拿数据
                col1, col2, col3, col4, col5, col_del = st.columns([1,2,2,2,2,1])
                col1.write(row["id"])
                col2.write(row["edate"])
                col3.write(row["item"])
                col4.write(abs(row["amount"]))
                col5.write(row["category"])
                if col_del.button("🗑️", key=f"del_{row['id']}"):
                    conn_exp.execute("DELETE FROM expense WHERE id = ?", (row["id"],))
                    conn_exp.commit()
                    st.success("已删除")
                    st.rerun()
        else:
            st.info("暂无数据")
    #对tab2进行可视化操作
    with tab2:
        # 月度趋势
        trend = pd.read_sql("""
            SELECT strftime('%Y-%m', edate) AS period,
                   SUM(ABS(amount)) AS total
            FROM expense
            GROUP BY period
            ORDER BY period
        """, conn_exp)
        if not trend.empty:
            fig = go.Figure(
                go.Scatter(x=trend["period"], y=trend["total"],
                           mode="lines+markers", name="支出")
            )
            fig.update_layout(title="月度支出趋势", xaxis_title="月份", yaxis_title="金额(元)")
            st.plotly_chart(fig, use_container_width=True)

        # 分类柱状图
        cat = pd.read_sql("""
            SELECT category, SUM(ABS(amount)) AS total
            FROM expense
            GROUP BY category
            ORDER BY total DESC
        """, conn_exp)
        if not cat.empty:
            st.bar_chart(cat.set_index("category"))
    # pd.read_sql 一条龙把下面的事全做了
    # 根据你给的 connection 对象 自动生成游标（内部调用 cursor()）。
    # 执行 cursor.execute(sql, params)。
    # fetchall() 把结果一次性取回。
    # 把取回的数据 转成 DataFrame 并返回。
    # 对tab1进行删除操作



    #返回登录按钮
    if st.button("返回登录"):
        st.session_state.page="login"
        st.session_state.pop("username", None)
        st.rerun()


if "page" not in st.session_state:
    st.session_state.page = "login"
if st.session_state.page == "login":
    login_page()
else:
    home_page()

